﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace FileTranslator
{
    public class SQLiteHelper : IDbComponent
    {
        public SQLiteConnection Connection { get; set; }
        public SQLiteCommand Command { get; set; }
        public SQLiteDataReader Reader { get; set; }
        public SQLiteTransaction Trans { get; set; }
        public SQLiteConnectionStringBuilder ConStrBuilder { get; set; }

        public string DataSource
        {
            get
            {
                return ConStrBuilder.DataSource;
            }
            set
            {
                ConStrBuilder.DataSource = value;
            }
        }

        public string Password
        {
            get
            {
                return ConStrBuilder.Password;
            }
            set
            {
                ConStrBuilder.Password = value;
            }
        }

        public string ConnectionString
        {
            get
            {
                return Connection.ConnectionString;
            }
            set
            {
                Connection.ConnectionString = value;
            }
        }


        public SQLiteHelper()
        {
            Connection = new SQLiteConnection();
            ConStrBuilder = new SQLiteConnectionStringBuilder();
            Command = Connection.CreateCommand();
            Reader = null;
            Trans = null;
        }

        private bool dbOpened = false;
        public bool Connect()
        {
            try
            {
                if (dbOpened == false)
                {
                    Connection.Open();
                    dbOpened = true;
                }
            }
            catch (System.Exception ex)
            {
                // TODO : log

                dbOpened = false;
            }

            return dbOpened;
        }

        public bool Disconnect()
        {
            bool success = false;
            if (dbOpened == true)
            {
                try
                {
                    Connection.Close();
                    dbOpened = false;

                    success = true;
                }
                catch (System.Exception ex)
                {
                    // TODO: log
                    dbOpened = true;
                }
            }

            return success;
        }

        public bool DbExists(string dbFile)
        {
            if (!System.IO.File.Exists(dbFile))
            {
                return false;
            }

            return true;
        }

        public int CopyTo(DataTable fromTable, string desTable)
        {
            int rowsAffacted = -1;
            try
            {
                SQLiteCommand comm = Connection.CreateCommand();
                comm.CommandText = "SELECT * FROM " + desTable;
                SQLiteDataAdapter oda = new SQLiteDataAdapter(comm);
                SQLiteCommandBuilder ocb = new SQLiteCommandBuilder(oda);
                oda.InsertCommand = ocb.GetInsertCommand();
                oda.DeleteCommand = ocb.GetDeleteCommand();
                oda.UpdateCommand = ocb.GetUpdateCommand();

                rowsAffacted = oda.Update(fromTable);
            }
            catch (SQLiteException ex)
            {
                // TODO: log error
            }
            catch (ArgumentNullException ex)
            {
                //MessageBox.Show("更新数据库文件失败[" + tableName + "]", "系统错误",
                //                MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

                // TODO: log error
            }
            catch (InvalidOperationException ex)
            {
                // TODO: log error
            }

            return rowsAffacted;
        }

        public bool Insert(string sql)
        {
            throw new NotImplementedException();
        }

        public bool DeleteAll(string tableName)
        {
            return Delete(tableName);
        }

        public bool Delete(string tableName, string where = null)
        {
            string commandStr = "DELETE FROM " + tableName;
            if (where != null)
            {
                commandStr += " WHERE " + where;
            }

            try
            {
                Command = Connection.CreateCommand();
                Command.CommandText = commandStr;
                
                Command.ExecuteNonQuery();

                return true;
            }
            catch (SQLiteException ex)
            {
                if (ex.ErrorCode == SQLiteErrorCode.NotFound)
                {
                    // 没有所要删除的表格
                    // TODO: log down
                }

                return false;
            }
        }

        public DataTable SelectAll(string tableName, string orderBy = "DESC")
        {
            return Select("*", tableName, null, orderBy);
        }

        public DataTable Select(string what, string tableName, string where = null, string orderBy = null, string groupBy = null, string having = null)
        {
            throw new NotImplementedException();
        }

        public void Dispose()
        {
            Disconnect();
        }

        public bool BeginTransaction()
        {
            try
            {
                Trans = Connection.BeginTransaction();
                return true;
            }
            catch (System.Exception ex)
            {
                // TODO: log
                return false;
            }

        }

        public bool Commit()
        {
            try
            {
                Trans.Commit();
                return true;
            }
            catch (System.Exception ex)
            {
                // TODO: log and prompt

                this.Rollback();
                return false;
            }
        }

        public bool Rollback()
        {
            try
            {
                Trans.Rollback();
                return true;
            }
            catch (System.Exception ex)
            {
                // TODO: log and exit

                return false;
            }
        }
    }
}
